﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using DataConnection.Interface;

namespace DataConnection.BE
{
    public class DataConnectionManager
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        string DataPath = string.Empty;
        /// <summary>
        /// SQL数据库链接
        /// </summary>
        SqlConnection sqlCon = null;

        SqlCommand sqlCom = null;
        /// <summary>
        /// 链接数据库
        /// </summary>
        public void GetSqlConnection()
        {
            try
            {
                //sqlCon = new SqlConnection("server=UFS\\SQLEXPRESS;DATABASE=CPData_001;USER ID=sa;PWD=123;Connect TimeOut=30;");
                sqlCon = new SqlConnection("server=LBWIN7\\MYSQL;DATABASE=CPData_001;USER ID=sa;PWD=123;Connect TimeOut=30;");
                if (sqlCon != null)
                {
                    sqlCom = sqlCon.CreateCommand();
                }
            }
            catch (Exception ex)
            { }
        }
        /// <summary>
        /// 打开数据库链接
        /// </summary>
        public void SqlConnectionOpen()
        {
            try
            {
                if (ConnectionState.Closed == sqlCon.State)
                {
                    sqlCon.Open();
                }
            }
            catch (Exception ex)
            { }
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void SqlConnectionClosed()
        {
            try
            {
                if (ConnectionState.Open == sqlCon.State)
                {
                    sqlCon.Close();
                }
            }
            catch (Exception ex)
            { 
            }
        }
        /// <summary>
        /// sql语句执行
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public object SqlExecuteString(string strSql)
        {
            try
            {
                sqlCom.CommandType = CommandType.Text;
                sqlCom.CommandText = strSql;
                SqlConnectionOpen();
                object ob= sqlCom.ExecuteScalar();
                SqlConnectionClosed();
                return ob;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                SqlConnectionClosed();
            }
            
        }
        /// <summary>
        /// 得到datata
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable SqlExecuteGetDataTable(string strSql)
        {
            try
            {
                SqlConnectionOpen();
                DataTable dtResult = new DataTable();
                sqlCom.CommandText = strSql;
                sqlCom.CommandType = CommandType.Text;
                SqlDataAdapter daAdapter = new SqlDataAdapter((SqlCommand)sqlCom);
                daAdapter.Fill(dtResult);
                return dtResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally 
            {
                SqlConnectionClosed();
            }

        }
    }
}
